﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using medicine.DAL.DataSet1TableAdapters;
using medicine.General;
using medicine.BaseComponents.Groups;
using System.Data.OleDb;

namespace medicine.DAL
{
    class MedicineDBStructure
    {
        public static DateTime? StudyDate = null;
        public static string GetGrandSelect(List<UnitsClass> Measures)
        {
            string Result= " "
                + " SELECT     Pharma_Medicine.intMedicine, Pharma_Molecule_Types_Level1.strMolecule_Level1, Pharma_Molecule_Types_Level1.strMoleculeDesc_Level1, "
                + "     Pharma_Molecule_Types_Level2.strMolecule_Level2, Pharma_Molecule_Types_Level2.strMoleculeDesc_Level2,  "
                + "     Pharma_Molecule_Types_Level3.strMolecule_Level3, Pharma_Molecule_Types_Level3.strMoleculeDesc_Level3,  "
                + "     Pharma_Molecule_Types_Level4.strMolecule_Level4, Pharma_Molecule_Types_Level4.strMoleculeDesc_Level4,  "
                + "     Pharma_Molecule_Types_Level5.strMolecule_Level5, Pharma_Molecule_Types_Level5.strMoleculeDesc_Level5, Pharma_Medicine.strMedicine,  "
                + "     Pharma_Agents.strAgent, Pharma_Medicine_Form.strForm, Pharma_Medicine_Form.curPrice, Pharma_Medicine_Form.strNew,  "
                + "     Pharma_MonthlySales.intYear, Pharma_MonthlySales.byteMonth, Pharma_MonthlySales.intForm, cdbl(Pharma_MonthlySales.curUnits) as curUnits,  "
                + "     Pharma_Manufactory.strManufactory, Pharma_Corporations.strCorporation,  "
                + "     Pharma_Medicine_Types.strMedicineType , cstr(Pharma_MonthlySales.intYear) +  cstr(Pharma_MonthlySales.byteMonth) AS SDate,"
                + "     cdbl( Pharma_MonthlySales.curValue) as curValue ,";
            
            for (int i = 0; i < Measures.Count ; i++)
            {
                if (Measures[i].isCurrency)
                {
                    Result += "cdbl( Pharma_MonthlySales.curValue * " + Measures[i].ExchangeRate.ToString() + ") as " + Measures[i].FieldName;
                    if (i != Measures.Count - 1)
                        Result += " ,";
                }
            }
            Result=Result.TrimEnd(',');

            Result += GrandFrom;

            return Result;

        }
        public static string GrandSelectForCount
        {
            get
            {
                return " SELECT COUNT(*) AS RecCount " + GrandFrom ;
            }
        }

        //public static string GrandSelect
        //{
        //    get
        //    {
        //        return " "
                    // + " SELECT       "
                    // + "   Pharma_Medicine.intMedicine " 
                    // + " , Pharma_Molecule_Types_Level1.strMolecule_Level1 " 
                    // + " , Pharma_Molecule_Types_Level1.strMoleculeDesc_Level1 " 
                    // + " , Pharma_Molecule_Types_Level2.strMolecule_Level2 " 
                    // + " , Pharma_Molecule_Types_Level2.strMoleculeDesc_Level2 " 
                    // + " , Pharma_Molecule_Types_Level3.strMolecule_Level3  " 
                    // + " , Pharma_Molecule_Types_Level3.strMoleculeDesc_Level3  " 
                    // + " , Pharma_Molecule_Types_Level4.strMolecule_Level4  " 
                    // + " , Pharma_Molecule_Types_Level4.strMoleculeDesc_Level4 " 
                    // + " , Pharma_Molecule_Types_Level5.strMolecule_Level5  " 
                    // + " , Pharma_Molecule_Types_Level5.strMoleculeDesc_Level5  " 
                    // + " , Pharma_Medicine.strMedicine " 
                    // + " , Pharma_Agents.strAgent " 
                    // + " , Pharma_Medicine_Form.strForm " 
                    // + " , Pharma_Medicine_Form.curPrice " 
                    // + " , Pharma_Medicine_Form.strNew " 
                    // + " , Pharma_MonthlySales.intYear " 
                    // + " , Pharma_MonthlySales.byteMonth " 
                    // + " , Pharma_MonthlySales.intForm " 
                    // + " , Pharma_MonthlySales.curUnits " 
                    // + " , Pharma_MonthlySales.curValue " 
                    // + " , Pharma_Manufactory.strManufactory " 
                    // + " , Pharma_Corporations.strCorporation " 
                    // + " , Pharma_Medicine_Types.strMedicineType " 
                    // + " FROM           " 
                    // + " Pharma_Medicine  " 
                    // + " INNER JOIN Pharma_Agents " 
                    // + " ON Pharma_Agents.intAgent = Pharma_Medicine.intAgent  " 
                    // + " INNER JOIN Pharma_Medicine_Form  " 
                    // + " ON Pharma_Medicine_Form.intMedicine = Pharma_Medicine.intMedicine  " 
                    // + " INNER JOIN Pharma_MonthlySales  " 
                    // + " ON Pharma_Medicine.intMedicine = Pharma_MonthlySales.intMedicine  " 
                    // + " INNER JOIN Pharma_Manufactory  " 
                    // + " ON Pharma_Medicine.intManufactory = Pharma_Manufactory.intManufactory  " 
                    // + " INNER JOIN Pharma_Corp_Manu  " 
                    // + " ON Pharma_Manufactory.intManufactory = Pharma_Corp_Manu.intManufactory  " 
                    // + " INNER JOIN Pharma_Corporations  " 
                    // + " ON Pharma_Corp_Manu.intCorporation = Pharma_Corporations.intCorporation  " 
                    // + " INNER JOIN Pharma_Medicine_Types  " 
                    // + " ON Pharma_Medicine.intMedicineType = Pharma_Medicine_Types.intMedicineType " 
                    // //+ " -- ******** (Molecule Level 5) " 
                    // + " INNER JOIN ( " 
                    // + " SELECT      " 
                    // + " Pharma_Molecule_Types.strMolecule as strMolecule_Level5 " 
                    // + " , Pharma_Molecule_Types.strMoleculeDesc as strMoleculeDesc_Level5 " 
                    // + " , Pharma_Molecule_Types.bMoleculeType as bMoleculeType_Level5 " 
                    // + " , Pharma_Molecule_Types.byteLevel as byteLevel_Level5 " 
                    // + " , Pharma_Molecule_Types.strParentMolecule as strParentMolecule_Level5 " 
                    // + " , Pharma_Medicine_Molecule.intMedicine " 
                    // + " FROM          " 
                    // + " Pharma_Molecule_Types  " 
                    // + " INNER JOIN Pharma_Medicine_Molecule  " 
                    // + " ON Pharma_Molecule_Types.strMolecule = Pharma_Medicine_Molecule.strMolecule " 
                    // + " where  " 
                    // + " byteLevel=5) as Pharma_Molecule_Types_Level5 " 
                    // + " ON Pharma_Medicine.intMedicine = Pharma_Molecule_Types_Level5.intMedicine " 
                    // //+ " -- ******** (Molecule Level 4) " 
                    // + " INNER JOIN ( " 
                    // + " SELECT      " 
                    // + " Pharma_Molecule_Types.strMolecule as strMolecule_Level4 " 
                    // + " , Pharma_Molecule_Types.strMoleculeDesc as strMoleculeDesc_Level4 " 
                    // + " , Pharma_Molecule_Types.bMoleculeType as bMoleculeType_Level4 " 
                    // + " , Pharma_Molecule_Types.byteLevel as byteLevel_Level4 " 
                    // + " , Pharma_Molecule_Types.strParentMolecule as strParentMolecule_Level4 " 
                    // + " FROM          " 
                    // + " Pharma_Molecule_Types  " 
                    // + " where  " 
                    // + " byteLevel=4) as Pharma_Molecule_Types_Level4 " 
                    // + " ON Pharma_Molecule_Types_Level5.strParentMolecule_Level5 = Pharma_Molecule_Types_Level4.strMolecule_Level4 " 
                    // //+ " ------ ******** (Molecule Level 3) " 
                    // + " INNER JOIN ( " 
                    // + " SELECT      " 
                    // + " Pharma_Molecule_Types.strMolecule as strMolecule_Level3 " 
                    // + " , Pharma_Molecule_Types.strMoleculeDesc as strMoleculeDesc_Level3 " 
                    // + " , Pharma_Molecule_Types.bMoleculeType as bMoleculeType_Level3 " 
                    // + " , Pharma_Molecule_Types.byteLevel as byteLevel_Level3 " 
                    // + " , Pharma_Molecule_Types.strParentMolecule as strParentMolecule_Level3 " 
                    // + " FROM          " 
                    // + " Pharma_Molecule_Types  " 
                    // + " where  " 
                    // + " byteLevel=3) as Pharma_Molecule_Types_Level3 " 
                    // + " ON Pharma_Molecule_Types_Level4.strParentMolecule_Level4 = Pharma_Molecule_Types_Level3.strMolecule_Level3 " 
                    // //+ " ------ ******** (Molecule Level 2) " 
                    // + " INNER JOIN ( " 
                    // + " SELECT      " 
                    // + " Pharma_Molecule_Types.strMolecule as strMolecule_Level2 " 
                    // + " , Pharma_Molecule_Types.strMoleculeDesc as strMoleculeDesc_Level2 " 
                    // + " , Pharma_Molecule_Types.bMoleculeType as bMoleculeType_Level2 " 
                    // + " , Pharma_Molecule_Types.byteLevel as byteLevel_Level2 " 
                    // + " , Pharma_Molecule_Types.strParentMolecule as strParentMolecule_Level2 " 
                    // + " FROM          " 
                    // + " Pharma_Molecule_Types  " 
                    // + " where  " 
                    // + " byteLevel=2) as Pharma_Molecule_Types_Level2 " 
                    // + " ON Pharma_Molecule_Types_Level3.strParentMolecule_Level3 = Pharma_Molecule_Types_Level2.strMolecule_Level2 " 
                    // //+ " ------ ******** (Molecule Level 1) " 
                    // + " INNER JOIN ( " 
                    // + " SELECT      " 
                    // + " Pharma_Molecule_Types.strMolecule as strMolecule_Level1 " 
                    // + " , Pharma_Molecule_Types.strMoleculeDesc as strMoleculeDesc_Level1 " 
                    // + " , Pharma_Molecule_Types.bMoleculeType as bMoleculeType_Level1 " 
                    // + " , Pharma_Molecule_Types.byteLevel as byteLevel_Level1 " 
                    // + " , Pharma_Molecule_Types.strParentMolecule as strParentMolecule_Level1 " 
                    // + " FROM          " 
                    // + " Pharma_Molecule_Types  " 
                    // + " where  " 
                    // + " byteLevel=1) as Pharma_Molecule_Types_Level1 " 
                    // + " ON Pharma_Molecule_Types_Level2.strParentMolecule_Level2 = Pharma_Molecule_Types_Level1.strMolecule_Level1 " ;
                    //// + " ---- " 
                    //// + " ---where Pharma_Molecule_Types_Level1.strMolecule='A' " 
        //            + " SELECT     Pharma_Medicine.intMedicine, Pharma_Molecule_Types_Level1.strMolecule_Level1, Pharma_Molecule_Types_Level1.strMoleculeDesc_Level1, "
        //            + "     Pharma_Molecule_Types_Level2.strMolecule_Level2, Pharma_Molecule_Types_Level2.strMoleculeDesc_Level2,  "
        //            + "     Pharma_Molecule_Types_Level3.strMolecule_Level3, Pharma_Molecule_Types_Level3.strMoleculeDesc_Level3,  "
        //            + "     Pharma_Molecule_Types_Level4.strMolecule_Level4, Pharma_Molecule_Types_Level4.strMoleculeDesc_Level4,  "
        //            + "     Pharma_Molecule_Types_Level5.strMolecule_Level5, Pharma_Molecule_Types_Level5.strMoleculeDesc_Level5, Pharma_Medicine.strMedicine,  "
        //            + "     Pharma_Agents.strAgent, Pharma_Medicine_Form.strForm, Pharma_Medicine_Form.curPrice, Pharma_Medicine_Form.strNew,  "
        //            + "     Pharma_MonthlySales.intYear, Pharma_MonthlySales.byteMonth, Pharma_MonthlySales.intForm, cdbl(Pharma_MonthlySales.curUnits) as curUnits,  "
        //            + "    cdbl( Pharma_MonthlySales.curValue) as curValue, Pharma_Manufactory.strManufactory, Pharma_Corporations.strCorporation,  "
        //            + "     Pharma_Medicine_Types.strMedicineType , cstr(Pharma_MonthlySales.intYear) +  cstr(Pharma_MonthlySales.byteMonth) AS SDate"
        //            + GrandFrom;
        //    }
        //}


        public static string GrandFrom
        {
            get
            {
                return " "
                    + " FROM         ((((((((((((Pharma_Medicine INNER JOIN "
                    + "     Pharma_Agents ON Pharma_Agents.intAgent = Pharma_Medicine.intAgent) INNER JOIN "
                    + "     Pharma_MonthlySales ON Pharma_Medicine.intMedicine = Pharma_MonthlySales.intMedicine) INNER JOIN "
                    + " ( SELECT  "
                    + "     Pharma_Medicine_Form.intMedicine, "
                    + "     Pharma_Medicine_Form.intForm "
                    + " 	,Pharma_Medicine_Form.strForm "
                    + " 	, Pharma_Medicine_Form.curPrice "
                    + " 	, Pharma_Medicine_Form.strNew "
                    + " 	,LEFT(Pharma_Medicine_Form.strNew,4) as strNewYear "
                    + " FROM "
                    + " 	Pharma_Medicine_Form ) AS "
                    + "     Pharma_Medicine_Form ON Pharma_Medicine_Form.intMedicine = Pharma_Medicine.intMedicine AND Pharma_Medicine_Form.intForm = Pharma_MonthlySales.intForm) INNER JOIN "
                    + "     Pharma_Manufactory ON Pharma_Medicine.intManufactory = Pharma_Manufactory.intManufactory) INNER JOIN "
                    + "     Pharma_Corp_Manu ON Pharma_Manufactory.intManufactory = Pharma_Corp_Manu.intManufactory) INNER JOIN "
                    + "     Pharma_Corporations ON Pharma_Corp_Manu.intCorporation = Pharma_Corporations.intCorporation) INNER JOIN "
                    + "     Pharma_Medicine_Types ON Pharma_Medicine.intMedicineType = Pharma_Medicine_Types.intMedicineType) INNER JOIN "
                    + "         (SELECT     Pharma_Molecule_Types.strMolecule AS strMolecule_Level5, Pharma_Molecule_Types.strMoleculeDesc AS strMoleculeDesc_Level5,  "
                    + "                                 Pharma_Molecule_Types.bMoleculeType AS bMoleculeType_Level5, Pharma_Molecule_Types.byteLevel AS byteLevel_Level5,  "
                    + "                                 Pharma_Molecule_Types.strParentMolecule AS strParentMolecule_Level5, Pharma_Medicine_Molecule.intMedicine "
                    + "             FROM         (Pharma_Molecule_Types INNER JOIN "
                    + "                                 Pharma_Medicine_Molecule ON Pharma_Molecule_Types.strMolecule = Pharma_Medicine_Molecule.strMolecule) "
                    + "             WHERE     (Pharma_Molecule_Types.byteLevel = 5)) Pharma_Molecule_Types_Level5 ON  "
                    + "     Pharma_Medicine.intMedicine = Pharma_Molecule_Types_Level5.intMedicine) INNER JOIN "
                    + "         (SELECT     strMolecule AS strMolecule_Level4, strMoleculeDesc AS strMoleculeDesc_Level4, bMoleculeType AS bMoleculeType_Level4,  "
                    + "                                 byteLevel AS byteLevel_Level4, strParentMolecule AS strParentMolecule_Level4 "
                    + "             FROM         Pharma_Molecule_Types Pharma_Molecule_Types_4 "
                    + "             WHERE     (byteLevel = 4)) Pharma_Molecule_Types_Level4 ON  "
                    + "     Pharma_Molecule_Types_Level5.strParentMolecule_Level5 = Pharma_Molecule_Types_Level4.strMolecule_Level4) INNER JOIN "
                    + "         (SELECT     strMolecule AS strMolecule_Level3, strMoleculeDesc AS strMoleculeDesc_Level3, bMoleculeType AS bMoleculeType_Level3,  "
                    + "                                 byteLevel AS byteLevel_Level3, strParentMolecule AS strParentMolecule_Level3 "
                    + "             FROM         Pharma_Molecule_Types Pharma_Molecule_Types_3 "
                    + "             WHERE     (byteLevel = 3)) Pharma_Molecule_Types_Level3 ON  "
                    + "     Pharma_Molecule_Types_Level4.strParentMolecule_Level4 = Pharma_Molecule_Types_Level3.strMolecule_Level3) INNER JOIN "
                    + "         (SELECT     strMolecule AS strMolecule_Level2, strMoleculeDesc AS strMoleculeDesc_Level2, bMoleculeType AS bMoleculeType_Level2,  "
                    + "                                 byteLevel AS byteLevel_Level2, strParentMolecule AS strParentMolecule_Level2 "
                    + "             FROM         Pharma_Molecule_Types Pharma_Molecule_Types_2 "
                    + "             WHERE     (byteLevel = 2)) Pharma_Molecule_Types_Level2 ON  "
                    + "     Pharma_Molecule_Types_Level3.strParentMolecule_Level3 = Pharma_Molecule_Types_Level2.strMolecule_Level2) INNER JOIN "
                    + "         (SELECT     strMolecule AS strMolecule_Level1, strMoleculeDesc AS strMoleculeDesc_Level1, bMoleculeType AS bMoleculeType_Level1,  "
                    + "                                 byteLevel AS byteLevel_Level1, strParentMolecule AS strParentMolecule_Level1 "
                    + "             FROM         Pharma_Molecule_Types Pharma_Molecule_Types_1 "
                    + "             WHERE     (byteLevel = 1)) Pharma_Molecule_Types_Level1 ON  "
                    + "     Pharma_Molecule_Types_Level2.strParentMolecule_Level2 = Pharma_Molecule_Types_Level1.strMolecule_Level1) ";


            }
        }
        public static string GrandJoin
        {
            get
            {
                return " "
                 //+ " Pharma_Medicine  "
                 //+ " INNER JOIN Pharma_Agents "
                 //+ " ON Pharma_Agents.intAgent = Pharma_Medicine.intAgent  "
                 //+ " INNER JOIN Pharma_Medicine_Form  "
                 //+ " ON Pharma_Medicine_Form.intMedicine = Pharma_Medicine.intMedicine  "
                 //+ " INNER JOIN Pharma_MonthlySales  "
                 //+ " ON Pharma_Medicine.intMedicine = Pharma_MonthlySales.intMedicine  "
                 //+ " INNER JOIN Pharma_Manufactory  "
                 //+ " ON Pharma_Medicine.intManufactory = Pharma_Manufactory.intManufactory  "
                 //+ " INNER JOIN Pharma_Corp_Manu  "
                 //+ " ON Pharma_Manufactory.intManufactory = Pharma_Corp_Manu.intManufactory  "
                 //+ " INNER JOIN Pharma_Corporations  "
                 //+ " ON Pharma_Corp_Manu.intCorporation = Pharma_Corporations.intCorporation  "
                 //+ " INNER JOIN Pharma_Medicine_Types  "
                 //+ " ON Pharma_Medicine.intMedicineType = Pharma_Medicine_Types.intMedicineType "
                 //   //+ " -- ******** (Molecule Level 5) " 
                 //+ " INNER JOIN ( "
                 //+ " SELECT      "
                 //+ " Pharma_Molecule_Types.strMolecule as strMolecule_Level5 "
                 //+ " , Pharma_Molecule_Types.strMoleculeDesc as strMoleculeDesc_Level5 "
                 //+ " , Pharma_Molecule_Types.bMoleculeType as bMoleculeType_Level5 "
                 //+ " , Pharma_Molecule_Types.byteLevel as byteLevel_Level5 "
                 //+ " , Pharma_Molecule_Types.strParentMolecule as strParentMolecule_Level5 "
                 //+ " , Pharma_Medicine_Molecule.intMedicine "
                 //+ " FROM          "
                 //+ " Pharma_Molecule_Types  "
                 //+ " INNER JOIN Pharma_Medicine_Molecule  "
                 //+ " ON Pharma_Molecule_Types.strMolecule = Pharma_Medicine_Molecule.strMolecule "
                 //+ " where  "
                 //+ " byteLevel=5) as Pharma_Molecule_Types_Level5 "
                 //+ " ON Pharma_Medicine.intMedicine = Pharma_Molecule_Types_Level5.intMedicine "
                 //   //+ " -- ******** (Molecule Level 4) " 
                 //+ " INNER JOIN ( "
                 //+ " SELECT      "
                 //+ " Pharma_Molecule_Types.strMolecule as strMolecule_Level4 "
                 //+ " , Pharma_Molecule_Types.strMoleculeDesc as strMoleculeDesc_Level4 "
                 //+ " , Pharma_Molecule_Types.bMoleculeType as bMoleculeType_Level4 "
                 //+ " , Pharma_Molecule_Types.byteLevel as byteLevel_Level4 "
                 //+ " , Pharma_Molecule_Types.strParentMolecule as strParentMolecule_Level4 "
                 //+ " FROM          "
                 //+ " Pharma_Molecule_Types  "
                 //+ " where  "
                 //+ " byteLevel=4) as Pharma_Molecule_Types_Level4 "
                 //+ " ON Pharma_Molecule_Types_Level5.strParentMolecule_Level5 = Pharma_Molecule_Types_Level4.strMolecule_Level4 "
                 //   //+ " ------ ******** (Molecule Level 3) " 
                 //+ " INNER JOIN ( "
                 //+ " SELECT      "
                 //+ " Pharma_Molecule_Types.strMolecule as strMolecule_Level3 "
                 //+ " , Pharma_Molecule_Types.strMoleculeDesc as strMoleculeDesc_Level3 "
                 //+ " , Pharma_Molecule_Types.bMoleculeType as bMoleculeType_Level3 "
                 //+ " , Pharma_Molecule_Types.byteLevel as byteLevel_Level3 "
                 //+ " , Pharma_Molecule_Types.strParentMolecule as strParentMolecule_Level3 "
                 //+ " FROM          "
                 //+ " Pharma_Molecule_Types  "
                 //+ " where  "
                 //+ " byteLevel=3) as Pharma_Molecule_Types_Level3 "
                 //+ " ON Pharma_Molecule_Types_Level4.strParentMolecule_Level4 = Pharma_Molecule_Types_Level3.strMolecule_Level3 "
                 //   //+ " ------ ******** (Molecule Level 2) " 
                 //+ " INNER JOIN ( "
                 //+ " SELECT      "
                 //+ " Pharma_Molecule_Types.strMolecule as strMolecule_Level2 "
                 //+ " , Pharma_Molecule_Types.strMoleculeDesc as strMoleculeDesc_Level2 "
                 //+ " , Pharma_Molecule_Types.bMoleculeType as bMoleculeType_Level2 "
                 //+ " , Pharma_Molecule_Types.byteLevel as byteLevel_Level2 "
                 //+ " , Pharma_Molecule_Types.strParentMolecule as strParentMolecule_Level2 "
                 //+ " FROM          "
                 //+ " Pharma_Molecule_Types  "
                 //+ " where  "
                 //+ " byteLevel=2) as Pharma_Molecule_Types_Level2 "
                 //+ " ON Pharma_Molecule_Types_Level3.strParentMolecule_Level3 = Pharma_Molecule_Types_Level2.strMolecule_Level2 "
                 //   //+ " ------ ******** (Molecule Level 1) " 
                 //+ " INNER JOIN ( "
                 //+ " SELECT      "
                 //+ " Pharma_Molecule_Types.strMolecule as strMolecule_Level1 "
                 //+ " , Pharma_Molecule_Types.strMoleculeDesc as strMoleculeDesc_Level1 "
                 //+ " , Pharma_Molecule_Types.bMoleculeType as bMoleculeType_Level1 "
                 //+ " , Pharma_Molecule_Types.byteLevel as byteLevel_Level1 "
                 //+ " , Pharma_Molecule_Types.strParentMolecule as strParentMolecule_Level1 "
                 //+ " FROM          "
                 //+ " Pharma_Molecule_Types  "
                 //+ " where  "
                 //+ " byteLevel=1) as Pharma_Molecule_Types_Level1 "
                 //+ " ON Pharma_Molecule_Types_Level2.strParentMolecule_Level2 = Pharma_Molecule_Types_Level1.strMolecule_Level1 ";


                + " ((((((((((((Pharma_Medicine INNER JOIN "
                + " Pharma_Agents ON Pharma_Agents.intAgent = Pharma_Medicine.intAgent) INNER JOIN "
                + " Pharma_MonthlySales ON Pharma_Medicine.intMedicine = Pharma_MonthlySales.intMedicine) INNER JOIN "
                + " ( SELECT  "
                + "     Pharma_Medicine_Form.intMedicine, "
                + "     Pharma_Medicine_Form.intForm "
                + " 	,Pharma_Medicine_Form.strForm "
                + " 	, Pharma_Medicine_Form.curPrice "
                + " 	, Pharma_Medicine_Form.strNew " 
                + " 	,LEFT(Pharma_Medicine_Form.strNew,4) as strNewYear "
                + " FROM "
                + " 	Pharma_Medicine_Form ) AS "
                + " Pharma_Medicine_Form ON Pharma_Medicine_Form.intMedicine = Pharma_Medicine.intMedicine AND Pharma_Medicine_Form.intForm = Pharma_MonthlySales.intForm) INNER JOIN "
                + " Pharma_Manufactory ON Pharma_Medicine.intManufactory = Pharma_Manufactory.intManufactory) INNER JOIN "
                + " Pharma_Corp_Manu ON Pharma_Manufactory.intManufactory = Pharma_Corp_Manu.intManufactory) INNER JOIN "
                + " Pharma_Corporations ON Pharma_Corp_Manu.intCorporation = Pharma_Corporations.intCorporation) INNER JOIN "
                + " Pharma_Medicine_Types ON Pharma_Medicine.intMedicineType = Pharma_Medicine_Types.intMedicineType) INNER JOIN "
                + "     (SELECT     Pharma_Molecule_Types.strMolecule AS strMolecule_Level5, Pharma_Molecule_Types.strMoleculeDesc AS strMoleculeDesc_Level5,  "
                + "                             Pharma_Molecule_Types.bMoleculeType AS bMoleculeType_Level5, Pharma_Molecule_Types.byteLevel AS byteLevel_Level5,  "
                + "                             Pharma_Molecule_Types.strParentMolecule AS strParentMolecule_Level5, Pharma_Medicine_Molecule.intMedicine "
                + "         FROM         (Pharma_Molecule_Types INNER JOIN "
                + "                             Pharma_Medicine_Molecule ON Pharma_Molecule_Types.strMolecule = Pharma_Medicine_Molecule.strMolecule) "
                + "         WHERE     (Pharma_Molecule_Types.byteLevel = 5)) Pharma_Molecule_Types_Level5 ON  "
                + " Pharma_Medicine.intMedicine = Pharma_Molecule_Types_Level5.intMedicine) INNER JOIN "
                + "     (SELECT     strMolecule AS strMolecule_Level4, strMoleculeDesc AS strMoleculeDesc_Level4, bMoleculeType AS bMoleculeType_Level4,  "
                + "                             byteLevel AS byteLevel_Level4, strParentMolecule AS strParentMolecule_Level4 "
                + "         FROM         Pharma_Molecule_Types Pharma_Molecule_Types_4 "
                + "         WHERE     (byteLevel = 4)) Pharma_Molecule_Types_Level4 ON  "
                + " Pharma_Molecule_Types_Level5.strParentMolecule_Level5 = Pharma_Molecule_Types_Level4.strMolecule_Level4) INNER JOIN "
                + "     (SELECT     strMolecule AS strMolecule_Level3, strMoleculeDesc AS strMoleculeDesc_Level3, bMoleculeType AS bMoleculeType_Level3,  "
                + "                             byteLevel AS byteLevel_Level3, strParentMolecule AS strParentMolecule_Level3 "
                + "         FROM         Pharma_Molecule_Types Pharma_Molecule_Types_3 "
                + "         WHERE     (byteLevel = 3)) Pharma_Molecule_Types_Level3 ON  "
                + " Pharma_Molecule_Types_Level4.strParentMolecule_Level4 = Pharma_Molecule_Types_Level3.strMolecule_Level3) INNER JOIN "
                + "     (SELECT     strMolecule AS strMolecule_Level2, strMoleculeDesc AS strMoleculeDesc_Level2, bMoleculeType AS bMoleculeType_Level2,  "
                + "                             byteLevel AS byteLevel_Level2, strParentMolecule AS strParentMolecule_Level2 "
                + "         FROM         Pharma_Molecule_Types Pharma_Molecule_Types_2 "
                + "         WHERE     (byteLevel = 2)) Pharma_Molecule_Types_Level2 ON  "
                + " Pharma_Molecule_Types_Level3.strParentMolecule_Level3 = Pharma_Molecule_Types_Level2.strMolecule_Level2) INNER JOIN "
                + "     (SELECT     strMolecule AS strMolecule_Level1, strMoleculeDesc AS strMoleculeDesc_Level1, bMoleculeType AS bMoleculeType_Level1,  "
                + "                             byteLevel AS byteLevel_Level1, strParentMolecule AS strParentMolecule_Level1 "
                + "         FROM         Pharma_Molecule_Types Pharma_Molecule_Types_1 "
                + "         WHERE     (byteLevel = 1)) Pharma_Molecule_Types_Level1 ON  "
                + " Pharma_Molecule_Types_Level2.strParentMolecule_Level2 = Pharma_Molecule_Types_Level1.strMolecule_Level1) ";



            }
        }
        
        public static DataSet1.TablesStructureDataTable  Structure;

        public static void Initilize()
        {
            Structure = new TablesStructureTableAdapter().GetData();
        }

        public static string GetSelectStatment(string TableName,string ValueField,string DisplayField,string QueryJoin,string QueryConditions)
        {
            string Result="";

            DataView dv = Structure.DefaultView;
            dv.RowFilter = "TableName = '" + TableName  + "'";

            if (dv.Count>0)
            {
                DataRow  drCurrent=dv.ToTable().Rows[0];
                Result = "SELECT DISTINCT " + drCurrent["TableName"] + "." + ValueField + " , " + drCurrent["TableName"] + "." + DisplayField + " ";
                Result+=" FROM " ;
                if (drCurrent["SQL"] != DBNull.Value && !string.IsNullOrEmpty(drCurrent["SQL"].ToString()))
                    Result += " (" + drCurrent["SQL"] + ") ";
                else
                    Result += " " + drCurrent["RealTableName"] + " ";
                Result += " AS " + drCurrent["TableName"] + " " ;
                if (QueryJoin != string.Empty)
                    Result += " " + QueryJoin +  " ";
                if (QueryConditions !=string.Empty)
                {
                    Result += " WHERE ";
                    Result += " " + QueryConditions + " ";
                }
                Result += " ORDER BY " + drCurrent["TableName"] + "." + DisplayField;

            }
            return Result;
        }
        public static string GetQueryStatment(string TableName,string ValueField, string DisplayField, string QueryConditions)
        {
            string Result = "";

            Result = "SELECT DISTINCT " + TableName + "." + ValueField + " , " + TableName + "." + DisplayField + " ";
            Result += " FROM " + GrandJoin;
            if (QueryConditions != string.Empty)
            {
                Result += " WHERE ";
                Result += " " + QueryConditions + " ";
            }
            Result += " ORDER BY " + TableName + "." + DisplayField;
            return Result;
        }
        public static DateTime?  GetStudyDate()
        {
            string SQL = " SELECT MAX(intYear) AS StudyYear, MAX(byteMonth) AS StudyMonth                           " +
                        " FROM            Pharma_MonthlySales                                                        " +
                        " WHERE        (intYear =                                                                    " +
                        "                             (SELECT        MAX(intYear) AS Expr1                          " +
                        "                                FROM            Pharma_MonthlySales Pharma_MonthlySales_1))"  +
                        " GROUP BY intYear                                                                           ";

             OleDbDataReader dr=OleDbUtilities.ExecuteDataReader(SQL);
            if (!dr.HasRows)
                return null;
            dr.Read();
            DateTime resulst = new DateTime(int.Parse(dr["StudyYear"].ToString()), (int)dr["StudyMonth"], 1);
            return resulst;
        }

        public static string GetAllRealtionsAsSQL(string SourceTable)
        {
            string Result = "";
            List<Relation> AllRelations=GetAllRelations(SourceTable);
            foreach (Relation item in AllRelations)
                Result += GetRelationAsSQL(item);

            return Result;
        }

        public static string GetRelationAsSQL(Relation relation)
        {
            string Result="";
            StructureTableRow Prev=null;
            foreach (StructureTableRow  item in relation.STR )
            {
                if (Prev != null) //First table will be the source table
                {
                    Result += " INNER JOIN (" + item.TableNameForSQL + ") AS " + item.TableName;
                    Result += "\r\n ON " + Prev.TableName + "." + Prev.RelationField + " = " + item.TableName + "." + Prev.RelationField;
                    Result += "\r\n ";
                }
                Prev = item;
            }
            return Result;
        }
        public static List<Relation> GetAllRelations(string SourceTable)
        {
            List<GroupLabel> FilteredGroups=MedicinUtilities.GetFilteredGroups();
            List<Relation> Result = new List<Relation>();
            foreach (GroupLabel item in FilteredGroups)
                if (item.GroupInfo.DBTableName!=SourceTable)
                    Result.Add(new Relation(GetRelations(SourceTable, item.GroupInfo.DBTableName), item.GroupInfo));

            return Result;
        }

        public static List<StructureTableRow> GetRelations(string SourceTable, string TargetTable)
        {
            
            DataView dv= Structure.DefaultView ;
            //DataView dv2 = Structure.DefaultView;
            //----------------------------------------------------------------------
            //if (real s name = real d name, filter on parent and child)
            dv.RowFilter = " TableName = '" + SourceTable + "'";
            DataRowView drSource = dv[0];
            dv.RowFilter = " TableName = '" + TargetTable + "'";
            DataRowView drTarget = dv[0];
            if (drSource["RealTableName"].ToString() == drTarget["RealTableName"].ToString())
            {

            }
            //----------------------------------------------------------------------
            StructureTableRow CurrentStructureTableRow = new StructureTableRow(drSource);
            List<StructureTableRow> StructureTableRows = new List<StructureTableRow>();
            StructureTableRows.Add(CurrentStructureTableRow);
            while (CurrentStructureTableRow.TableName != TargetTable && CurrentStructureTableRow.ParentID != string.Empty )
            {
                dv.RowFilter = "ID = " + CurrentStructureTableRow.ParentID;
                CurrentStructureTableRow = new StructureTableRow(dv[0]);
                StructureTableRows.Add(CurrentStructureTableRow);
            }

            List<StructureTableRow> StructureTableRowsReverse = new List<StructureTableRow>();
            CurrentStructureTableRow = new StructureTableRow(drTarget);
            StructureTableRowsReverse.Add(CurrentStructureTableRow);
            while (CurrentStructureTableRow.ParentID != string.Empty)
            {
                dv.RowFilter = "ID = " + CurrentStructureTableRow.ParentID;
                CurrentStructureTableRow = new StructureTableRow(dv[0]);
                if (CurrentStructureTableRow.ParentID != string.Empty)
                    StructureTableRowsReverse.Add(CurrentStructureTableRow);
            }

            for (int i = StructureTableRowsReverse.Count - 1; i >= 0; i--)
            {
                StructureTableRows[StructureTableRows.Count - 1].RelationField = StructureTableRowsReverse[i].RelationField;
                StructureTableRows.Add(StructureTableRowsReverse[i]);
            }
            //----------------------------------------------------------------------
            return StructureTableRows;
        }


    }
    public class Relation
    {
        public List<StructureTableRow> STR;
        public GroupInfo GI;
        public Relation(List<StructureTableRow> STR, GroupInfo GI)
        {
            this.STR = STR;
            this.GI = GI;
        }
    }
    public class StructureTableRow
    {
        public string ParentID;
        public string TableName;
        public string RelationField;
        public string ForignField;
        public string TableNameForSQL;
        //public string RealTableName;
        public DataRowView dr;
        public StructureTableRow(DataRowView dr)
        {
            this.dr = dr;
            ParentID = dr["Parent"].ToString();
            TableName = dr["TableName"].ToString();
            //RealTableName= dr["RealTableName"].ToString();
            RelationField = dr["RelationField"].ToString();
            ForignField = dr["ForignRelationField"].ToString();
            if (dr["SQL"].ToString() == string.Empty)
                TableNameForSQL = dr["RealTableName"].ToString();
            else
                TableNameForSQL = dr["SQL"].ToString();
        }
    }
}
